﻿using Common;
using DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BaseDal.Base
{
    public class BaseDal<T> where T : class, new()
    {
        // 表名
        protected string tableName;

        // 构造函数，传入具体的表名
        public BaseDal(string tableName)
        {
            this.tableName = tableName;
        }

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append($"SELECT COUNT(1) FROM {tableName} WHERE IsDelete=0 {strWhere}");
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 获取查询SQL语句
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <returns>SQL语句</returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append($"SELECT * FROM {tableName} WHERE IsDelete=0");
            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }
            return sb;
        }

        /// <summary>
        /// 查询实体列表
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <param name="filedOrder">排序字段</param>
        /// <returns>实体列表</returns>
        public List<T> GetList(string strWhere, string filedOrder)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            sb.Append($" ORDER BY {filedOrder}");
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<T>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据ID查询实体
        /// </summary>
        /// <param name="id">ID</param>
        /// <returns>实体对象</returns>
        public T GetModelById(int id)
        {
            StringBuilder sb = GetSelectSql(" AND ID=@id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id", id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return CommonOperation.DataTableToModel<T>(ds.Tables[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 添加实体
        /// </summary>
        /// <param name="model">实体对象</param>
        /// <returns>受影响的行数</returns>
        public int Add(T model, string insertSql, SqlParameter[] parameters)
        {
            return SqlHelper.ExecuteSql(insertSql, parameters);
        }

        /// <summary>
        /// 更新实体
        /// </summary>
        /// <param name="model">实体对象</param>
        /// <param name="updateSql">更新SQL</param>
        /// <param name="parameters">SQL参数</param>
        /// <returns>受影响的行数</returns>
        public int Update(T model, string updateSql, SqlParameter[] parameters)
        {
            return SqlHelper.ExecuteSql(updateSql, parameters);
        }

        /// <summary>
        /// 删除实体
        /// </summary>
        /// <param name="id">ID</param>
        /// <param name="deleteSql">删除SQL</param>
        /// <param name="parameters">SQL参数</param>
        /// <returns>受影响的行数</returns>
        public int Delete(int id, string deleteSql, SqlParameter[] parameters)
        {
            return SqlHelper.ExecuteSql(deleteSql, parameters);
        }
    }
}
